package javaroke.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;


import javaroke.excepcion.DAOExcepcion;
import javaroke.modelo.Comprobante;
import javaroke.modelo.ComprobanteImpr;
import javaroke.modelo.DetalleComprobante;
import javaroke.modelo.Evento;
import javaroke.util.ConexionBD;

public class ComprobanteDAO extends BaseDAO{

	public Comprobante insertar(Comprobante vo) throws DAOExcepcion {
		String query = "insert into comprobante (fecha,monto,idcajero) values (?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			con.setAutoCommit(false);
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getFecha());
			stmt.setDouble(2, vo.getMonto());
			stmt.setInt(3, vo.getCajero().getIdCajero());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setIdComprobante(id);
			
			//Insercion del detalle de comprobante
			for (DetalleComprobante ped : vo.getDetalles()) {
				query = "INSERT INTO detalleComprobante(idComprobante, idproducto, cantidad) VALUES (?,?,?)";
				stmt = con.prepareStatement(query);
				stmt.setInt(1, id);
				stmt.setInt(2, ped.getProducto().getIdProducto());
				stmt.setInt(3, ped.getCantidad());

				int u = stmt.executeUpdate();
				if (u != 1) {
					throw new SQLException("No se pudo insertar");
				}
				int idd = 0;
				query = "SELECT LAST_INSERT_ID()";
				stmt = con.prepareStatement(query);
				rs = stmt.executeQuery();
				if (rs.next()) {
					idd = rs.getInt(1);
				}
				ped.setIdcomprobante(idd);
			}
			con.commit();			

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
	
	public Collection<ComprobanteImpr> buscarPorNombre(int id)
			throws DAOExcepcion {
		String query = " select a.idcomprobante, concat(f.nombres,' ',f.paterno,' ', f.materno) as nombre, c.nombre as producto, "; 
		  query += " a.monto, a.fecha, ifnull(g.ruc,' ') as RUC ";
		  query += " from comprobante a inner join ";
		  query += " detallecomprobante b on a.idcomprobante = b.idcomprobante inner join "; 
		  query += " producto c on b.idproducto = c.idproducto inner join "; 
		  query += " pedido d on d.idcomprobante = a.idcomprobante inner join "; 
		  query += " cliente e on e.idcliente = d.idcliente inner join ";
		  query += " persona f on f.dni = e.dni left join " ;
		  query += " factura g on a.idcomprobante = g.idcomprobante left join "; 
		  query += " boleta h on a.idcomprobante = h.idcomprobante "; 
		  query += " where a.idcomprobante = ? "	;				
		
		Collection<ComprobanteImpr> lista = new ArrayList<ComprobanteImpr>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ComprobanteImpr vo = new ComprobanteImpr();
				vo.setIdComprobante(rs.getInt("idcomprobante"));
				vo.setNombre(rs.getString("nombre"));
				vo.setProducto(rs.getString("producto"));
				vo.setMonto(rs.getDouble("monto"));				
				vo.setFecha(rs.getString("fecha"));
				vo.setRuc(rs.getString("ruc"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return lista;
	}	
	
}
